﻿Imports System
Imports System.Data
Imports System.Data.OleDb
Imports System.IO
Imports DevExpress.XtraGrid
Imports DevExpress.XtraGrid.Views.Grid
Imports DevExpress.XtraGrid.Views.Grid.ViewInfo




Public Class DataSourceForm

    Private dataAdapterM3 As New OleDb.OleDbDataAdapter()

    Private Sub DataSourceForm_FormClosing(sender As Object, e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
        'Save all gridview setups
        If Not Directory.Exists(My.Settings.ConfigPath) Then Directory.CreateDirectory(My.Settings.ConfigPath)
        Try
            GridView1.SaveLayoutToXml(My.Settings.ConfigPath & "\DataSourceFormGW01.xml")
        Catch ex As Exception
        End Try

        My.Settings.DataSourceFormPos = Me.Location
        My.Settings.DataSourceFormSize = Me.Size
    End Sub

    Private Sub DataSourceForm_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load

        Me.Location = My.Settings.DataSourceFormPos
        FormPos(My.Settings.DataSourceFormSize)
        Me.Size = My.Settings.DataSourceFormSize

        GetDataM3("SELECT * FROM F4002 ")


    End Sub


    Function GetDataM3(ByVal selectCommand As String)

        Try

            Dim ConCM As OleDb.OleDbConnection
            ConCM = New OleDb.OleDbConnection(My.Settings.M3ConnectUser)
            ConCM.Open()

            Dim cmd As New OleDbCommand(selectCommand, ConCM)

            Me.dataAdapterM3 = New OleDb.OleDbDataAdapter(cmd)
            Dim table As New DataTable
            table.Locale = System.Globalization.CultureInfo.InvariantCulture
            Me.dataAdapterM3.Fill(table)
            Me.BindingSource1.DataSource = table

            ' Resize the DataGridView columns to fit the newly loaded content.
            Me.GridView1.BestFitColumns()

            'Restore all gridview setups
            If File.Exists(My.Settings.ConfigPath & "\DataSourceFormGW01.xml") Then GridView1.RestoreLayoutFromXml(My.Settings.ConfigPath & "\DataSourceFormGW01.xml")

        Catch ex As OleDbException

        End Try
        GetDataM3 = ""

    End Function

    Private Sub BarButtonItem1_ItemClick(sender As System.Object, e As DevExpress.XtraBars.ItemClickEventArgs) Handles BarButtonItem1.ItemClick
        Dim cmdBuilder As New OleDb.OleDbCommandBuilder(dataAdapterM3)

        Me.dataAdapterM3.Update(CType(Me.BindingSource1.DataSource, DataTable))
    End Sub

    Private Sub BarButtonItem2_ItemClick(sender As System.Object, e As DevExpress.XtraBars.ItemClickEventArgs) Handles BarButtonItem2.ItemClick
        'Load data to excel
        Const filename As String = "c:\JLD004_DataSourceForm_Data.xls"

        If File.Exists(filename) Then
            File.Delete(filename)
        End If

        GridControl1.ExportToXls(filename)

        Dim app = New Microsoft.Office.Interop.Excel.Application
        app.Visible = True

        app.Workbooks.Open(System.IO.Path.GetFullPath(filename))
    End Sub
End Class